﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Vit.Database.Schema
{
    [Serializable]
    public class DatabaseSchema : SchemaObjectBase
    {
        private DatabaseWrapper databaseWrapper;

        public DatabaseSchema(DatabaseWrapper dbWrapper)
        {
            Schemas = new List<SchemaSchema>();
            databaseWrapper = dbWrapper;
        }

        public void Refresh(IEnumerable<ColumnSchemaInfo> columnInfos, IEnumerable<ParameterSchemaInfo> paraInfos)
        {
            Schemas.Clear();
            foreach (ColumnSchemaInfo ci in columnInfos)
            {
                SchemaSchema schema = FindAndAddIfNotExists(Schemas,
                    o => string.Equals(o.Name, ci.SchemaName, StringComparison.OrdinalIgnoreCase),
                    (SchemaSchema o) =>
                    {
                        o.Database = this;
                        o.Owner = ci.OwnerName;
                        o.Name = ci.SchemaName;
                    });

                switch (ci.ObjectType)
                {
                    case TabularObjectType.Table:
                        {
                            TableSchema table = FindAndAddIfNotExists(schema.Tables,
                                o => string.Equals(o.Name, ci.ObjectName, StringComparison.OrdinalIgnoreCase),
                                (TableSchema o) =>
                                {
                                    o.Schema = schema;
                                    o.Owner = ci.OwnerName;
                                    o.Name = ci.ObjectName;
                                    o.Description = ci.ObjectDescription;
                                });

                            TableColumnSchema tblCol = FindAndAddIfNotExists(table.Columns,
                                o => string.Equals(o.Name, ci.ColumnName, StringComparison.OrdinalIgnoreCase),
                                (TableColumnSchema o) =>
                                {
                                    o.Schema = schema;
                                    o.Owner = ci.OwnerName;
                                    o.Name = ci.ColumnName;
                                    o.Description = ci.ColumntDescription;
                                    o.AllowDBNull = ci.AllowDBNull;
                                    o.NativeType = ci.NativeType;
                                    o.Precision = ci.Precision;
                                    o.Size = ci.Size;
                                    o.Scale = ci.Size;
                                    o.Order = ci.ColumnOrder;
                                    o.IsPrimaryKeyMember = !string.IsNullOrEmpty(ci.PrimaryKeyName);
                                    o.IsForeignKeyMember = !string.IsNullOrEmpty(ci.ForeignKeyName);
                                    o.IsIdentity = ci.IsIdentity;
                                    o.IsUnique = ci.IsIdentity || !string.IsNullOrEmpty(ci.PrimaryKeyName);
                                });

                            if (tblCol.IsPrimaryKeyMember)
                            {
                                PrimaryKeySchema pks = FindAndAddIfNotExists(table.PrimaryKeys,
                                    o => string.Equals(o.Name, ci.PrimaryKeyName, StringComparison.OrdinalIgnoreCase),
                                    (PrimaryKeySchema o) =>
                                    {
                                        o.Schema = schema;
                                        o.Owner = ci.OwnerName;
                                        o.Name = ci.PrimaryKeyName;
                                        o.Table = table;
                                    });


                                pks.KeyColumns.Add(tblCol);
                            }

                            if (tblCol.IsForeignKeyMember)
                            {
                                ForeignKeySchema fks = FindAndAddIfNotExists(table.ForeignKeys,
                                    o => string.Equals(o.Name, ci.ForeignKeyName, StringComparison.OrdinalIgnoreCase),
                                    (ForeignKeySchema o) =>
                                    {
                                        o.Schema = schema;
                                        o.Owner = ci.OwnerName;
                                        o.Name = ci.ForeignKeyName;
                                        o.PrimaryTableName = ci.ForeignKeyTableName;
                                        o.ForeignTableName = table.Name;
                                    });

                                ForeignKeyColumnSchema fkcs = FindAndAddIfNotExists<ForeignKeyColumnSchema>(fks.KeyColumns,
                                    o => string.Equals(o.PrimaryTableColumnName, ci.ForeignKeyColumnName, StringComparison.OrdinalIgnoreCase) && string.Equals(o.ForeignTableColumnName, tblCol.Name, StringComparison.OrdinalIgnoreCase),
                                    (ForeignKeyColumnSchema o) =>
                                    {
                                        o.ForeignKeySchema = fks;
                                        o.PrimaryTableColumnName = ci.ForeignKeyColumnName;
                                        o.ForeignTableColumnName = tblCol.Name;
                                    });
                            }

                            break;
                        }
                    case TabularObjectType.View:
                        {
                            ViewSchema view = FindAndAddIfNotExists(schema.Views,
                               o => string.Equals(o.Name, ci.ObjectName, StringComparison.OrdinalIgnoreCase),
                               (ViewSchema o) =>
                               {
                                   o.Name = ci.ObjectName;
                                   o.Schema = schema;
                                   o.Owner = ci.OwnerName;
                                   o.Description = ci.ObjectDescription;
                               });

                            ViewColumnSchema viewCol = FindAndAddIfNotExists(view.Columns,
                                o => string.Equals(o.Name, ci.ColumnName, StringComparison.OrdinalIgnoreCase),
                                (ViewColumnSchema o) =>
                                {
                                    o.Schema = schema;
                                    o.Owner = ci.OwnerName;
                                    o.Name = ci.ColumnName;
                                    o.Description = ci.ColumntDescription;
                                    o.AllowDBNull = ci.AllowDBNull;
                                    o.NativeType = ci.NativeType;
                                    o.Precision = ci.Precision;
                                    o.Size = ci.Size;
                                    o.Scale = ci.Size;
                                    o.Order = ci.ColumnOrder;
                                });

                            break;
                        }
                    default:
                        break;
                }
            }

            foreach (ParameterSchemaInfo pi in paraInfos)
            {
                SchemaSchema schema = FindAndAddIfNotExists(Schemas,
                    o => string.Equals(o.Name, pi.SchemaName, StringComparison.OrdinalIgnoreCase),
                    (SchemaSchema o) =>
                    {
                        o.Database = this;
                        o.Owner = pi.OwnerName;
                        o.Name = pi.SchemaName;
                    });

                switch (pi.ObjectType)
                {
                    case CommandObjectType.StoredProcedure:
                        {
                            StoredProcedureSchema sp = FindAndAddIfNotExists(schema.StoredProcedures,
                                o => string.Equals(o.Name, pi.ObjectName, StringComparison.OrdinalIgnoreCase),
                                (StoredProcedureSchema o) =>
                                {
                                    o.Schema = schema;
                                    o.Owner = pi.OwnerName;
                                    o.Name = pi.ObjectName;
                                    o.CommandText = pi.ObjectName;
                                });

                            ParameterSchema spPara = FindAndAddIfNotExists(sp.Parameters,
                                o => string.Equals(o.Name, pi.ParameterName, StringComparison.OrdinalIgnoreCase),
                                (ParameterSchema o) =>
                                {
                                    o.Schema = schema;
                                    o.Owner = pi.OwnerName;
                                    o.Name = pi.ParameterName;
                                    o.Command = sp;
                                    o.Order = pi.ParameterOrder;
                                    o.NativeType = pi.NativeType;
                                    o.Precision = pi.Precision;
                                    o.Size = pi.Size;
                                    o.Scale = pi.Size;
                                    o.Direction = pi.Direction;
                                });

                            break;
                        }
                    case CommandObjectType.Function:
                        {
                            FunctionSchema func = FindAndAddIfNotExists(schema.Functions,
                               o => string.Equals(o.Name, pi.ObjectName, StringComparison.OrdinalIgnoreCase),
                               (FunctionSchema o) =>
                               {
                                   o.Schema = schema;
                                   o.Owner = pi.OwnerName;
                                   o.Name = pi.ObjectName;
                                   o.CommandText = pi.ObjectName;
                               });

                            ParameterSchema funcPara = FindAndAddIfNotExists(func.Parameters,
                                o => string.Equals(o.Name, pi.ParameterName, StringComparison.OrdinalIgnoreCase),
                                (ParameterSchema o) =>
                                {
                                    o.Schema = schema;
                                    o.Owner = pi.OwnerName;
                                    o.Name = pi.ParameterName;
                                    o.Command = func;
                                    o.Order = pi.ParameterOrder;
                                    o.NativeType = pi.NativeType;
                                    o.Precision = pi.Precision;
                                    o.Size = pi.Size;
                                    o.Scale = pi.Size;
                                    o.Direction = pi.Direction;
                                });

                            break;
                        }
                    default:
                        break;
                }
            }
        }

        private T FindAndAddIfNotExists<T>(IList<T> list, Func<T, bool> predict, Action<T> initialize) where T : SchemaObjectBase, new()
        {
            T obj = list.FirstOrDefault(predict);
            if (obj == null)
            {
                obj = new T();

                if (initialize != null)
                    initialize(obj);

                list.Add(obj);
            }
            return obj;
        }

        public SchemaSchema GetSchema(string schemaName)
        {
            return Schemas.FirstOrDefault(o => string.Equals(o.Name, schemaName, StringComparison.OrdinalIgnoreCase));
        }

        public TableSchema GetTable(string schemaName, string tableName)
        {
            SchemaSchema schema = GetSchema(schemaName);
            return schema.Tables.FirstOrDefault(o => string.Equals(o.Name, tableName, StringComparison.OrdinalIgnoreCase));
        }

        public ViewSchema GetView(string schemaName, string viewName)
        {
            SchemaSchema schema = GetSchema(schemaName);
            return schema.Views.FirstOrDefault(o => string.Equals(o.Name, viewName, StringComparison.OrdinalIgnoreCase));
        }

        public StoredProcedureSchema GetStoredProcedure(string schemaName, string storedProcedureName)
        {
            SchemaSchema schema = GetSchema(schemaName);
            return schema.StoredProcedures.FirstOrDefault(o => string.Equals(o.Name, storedProcedureName, StringComparison.OrdinalIgnoreCase));
        }

        public FunctionSchema GetFunction(string schemaName, string functionName)
        {
            SchemaSchema schema = GetSchema(schemaName);
            return schema.Functions.FirstOrDefault(o => string.Equals(o.Name, functionName, StringComparison.OrdinalIgnoreCase));
        }

        public List<SchemaSchema> Schemas { get; set; }

        public DatabaseWrapper DatabaseWrapper { get { return databaseWrapper; } }
    }
}
